const express = require('express');
const router = express.Router();
const db = require('../db/index');
const moment = require('moment');

// 数据中间件
router.use((req, res, next) => {
  // let nav;

  var navSql = `SELECT * FROM nav WHERE is_show=1`;
  db.query(navSql, (err, results) => {
    if (err) return res.send({ code: 0, msg: err.message });
    if (results.length != 0) {
      nav = results;
    }
  });
  var footSql = `SELECT * FROM companyinformation,qualifications`;
  db.query(footSql, (err, results) => {
    if (err) return res.send({ code: 0, msg: err.message });
    if (results.length != 0) {
      foot = results[0];
      // console.log(foot);
    }
  });
  setTimeout(function () {
    res.state = nav;
    res.state = foot[0];
  }, 2000);
  next();
});

// 首页
router.get('/', (req, res) => {
  var banner;
  var product;
  var article;
  var productClass;
  // res.send('首页')

  var bannerSql = `SELECT * FROM banner WHERE banner_show=1`;
  db.query(bannerSql, (err, results) => {
    if (err) {
      return res.send({ code: 0, msg: err.message });
    }
    if (results.length != 0) {
      // 查到了数据
      // console.log(results);
      banner = results;
    }
  });
  // 查询项目分类
  var projectClassSql = `SELECT * FROM product_classification WHERE is_delete=0`;
  db.query(projectClassSql, (err, results) => {
    if (err) {
      return res.send({ code: 0, msg: err.message });
    }
    if (results.length != 0) {
      // 查到了数据
      // console.log(results);
      productClass = results;
      // console.log(productClass);
    }
  });

  var projectSql = `SELECT * FROM product_information_management LIMIT 4`;
  db.query(projectSql, (err, results) => {
    if (err) {
      return res.send({ code: 0, msg: err.message });
    }
    if (results.length != 0) {
      // 查到了数据
      // console.log(results);
      product = results;
    }
  });
  var articleSql = `SELECT * FROM article WHERE is_show=1 ORDER BY article_id DESC LIMIT 4`;
  db.query(articleSql, (err, results) => {
    if (err) {
      return res.send({ code: 0, msg: err.message });
    }
    if (results.length != 0) {
      // 查到了数据
      // results[0].article_date:2022-07-13T17:14:18.000Z,
      results.forEach(item => {
        item.article_date = item.article_date.toLocaleDateString().split('/').slice(1).join('-');
      });
      // console.log(results);
      article = results;
    }
  });
  setTimeout(() => {
    res.render('index/index', { banner, product, article, productClass });
    // console.log(nav, banner);
    // console.log(article);
  }, 50);
});

// 项目案例  /project
router.get('/project', (req, res) => {
  var { id = 0, limit = 4, page = 1 } = req.query;
  // console.log(req.query);
  // 项目详情
  let project;
  // limit  每页的数据条数
  // let limit = 4;
  // 页数
  let countPage;
  let offset = (page - 1) * limit;
  let sql;
  let sqlCount;
  // 实现分页  查询数据总条数

  // 在首页点击产品中心时  发来的id为0  故这时就要判断
  /* 
  分页  page=2&limit=5
  limit offset(page-1*limit),limit
  1.limit 0,5
  2.limit 5,5
  3.limit 10,5
  */
  let projectClass;
  // 查询产品分类
  var classifySql = `SELECT * FROM product_classification WHERE is_delete=0`;
  db.query(classifySql, (err, results) => {
    if (err) {
      return res.send({ code: 0, msg: err.message });
    }
    if (results.length != 0) {
      projectClass = results;
      // console.log(projectClass);
    }
  });

  if (id == 0) {
    // 在首页点击nav产品
    sql = `SELECT * FROM product_information_management WHERE is_delete=0 limit ${offset},${limit}`;
    // 查询总的数据条数
    sqlCount = `SELECT count(*) AS count FROM product_information_management WHERE is_delete=0 `;
  } else {
    sql = `SELECT * FROM product_information_management WHERE pc_id=${id} AND is_delete=0 limit ${offset},${limit}`;
    sqlCount = `SELECT count(*) AS count FROM product_information_management WHERE pc_id=${id}  AND is_delete=0 `;
  }
  // 查询数据条数
  db.query(sqlCount, (err, results) => {
    if (err) {
      return res.send({ code: 0, msg: err.message });
    }
    if (results.length != 0) {
      // 查到了数据
      // return res.send({ code: 1, msg: '查询成功', data: results });
      countPage = Math.ceil(results[0].count / limit);

      // console.log(countPage);
    } else {
      return res.send({ code: 0, msg: '查询失败' });
    }
  });
  // 查询数据
  db.query(sql, (err, results) => {
    if (err) {
      return res.send({ code: 0, msg: err.message });
    }
    if (results.length != 0) {
      // 查到了数据
      // return res.send({ code: 1, msg: '查询成功', data: results });
      project = results;
      // console.log(project);
    } else {
      return res.send({ code: 0, msg: '查询失败' });
    }
  });
  setTimeout(() => {
    // TODO:
    // res.render('index/productcenter', { project, countPage, id, page, projectClass });
    res.render('index/productcenter', { project, countPage, id, page, projectClass });
  }, 50);
});

// router  详情商品
router.get('/productydetails', (req, res) => {
  // 获取前台传来的商品详情对应的ID
  let { id } = req.query;
  let detail;
  let upTo;
  let downTo;
  // 为你推荐
  let recommend;
  const sql = `SELECT product_information_management.*,product_classification.pc_name FROM product_information_management,product_classification WHERE product_classification.pc_id=product_information_management.pc_id AND pi_id=${id}`;
  // console.log(id, sql);
  db.query(sql, (err, results) => {
    if (err) {
      return res.send({ code: 0, msg: err.message });
    }
    if (results.length != 0) {
      // 查到了数据
      // results.forEach(item => {
      //   item.article_date = item.article_date.toLocaleDateString().split('/').slice(1).join('-');
      // });
      detail = results[0];
      detail.pi_thumb = detail.pi_thumb.split(',');
      // console.log(detail);
    } else {
      // 没查到数据
      return res.send({ code: 1, msg: '查询失败' });
    }
  });

  // 上一篇
  var sqlUpto = `SELECT pi_id,pi_name FROM product_information_management WHERE pi_id<${id} ORDER BY pi_id DESC LIMIT 1`;
  db.query(sqlUpto, (err, results) => {
    if (err) {
      return res.send({ code: 0, msg: err.message });
    }
    if (results.length != 0) {
      // 查到了数据
      upTo = results[0];
    } else {
      // 没查到数据
      upTo = { pi_id: id, pi_name: ' 没有了' };
      // return res.send({ code: 0, msg: '查询失败' });
    }
  });
  // 下一篇
  var sqlDownTo = `SELECT pi_id,pi_name FROM product_information_management WHERE pi_id>${id} LIMIT 1`;
  db.query(sqlDownTo, (err, results) => {
    if (err) {
      return res.send({ code: 0, msg: err.message });
    }
    if (results.length != 0) {
      // 查到了数据
      downTo = results[0];
      // console.log(downTo);
    } else {
      // 没查到数据  说明没有数据了  可能是最后一篇；
      // return res.send({ code: 0, msg: '查询失败' });
      downTo = { pi_id: id, pi_name: ' 没有了' };
    }
  });

    // 为您推荐
    var recommendSql = `SELECT * FROM product_information_management WHERE pi_id!=${id} LIMIT 4`;
    db.query(recommendSql, (err, results) => {
      if (err) {
        return res.send({ code: 0, msg: err.message });
      }
      if (results.length != 0) {
        // 查到了数据
        recommend = results;
      } else {
        // 没查到数据
        // upTo = { pi_id: id, pi_name: ' 没有了' };
        return res.send({ code: 0, msg: '查询失败' });
      }
    });

  setTimeout(() => {
    // console.log(recommend);
    res.render('index/product_details', { detail, upTo, downTo,recommend });
    // res.render('index/product_details', { detail });
    // console.log(detail);
  }, 500);
});

// 产品列表页重写
router.get('/list', (req, res) => {
  res.render('index/list');
});

// 所有文章列表  /news
router.get('/articlelist', (req, res) => {
  let { id } = req.query;
  let data = [],
    news = [],
    sql,
    count = 0,
    page = 1,
    quick = [];
  if (req.query.page && req.query.page > 0) page = req.query.page;
  let pagea = (page - 1) * 5,
    pageb = 5;
  sql = `SELECT * FROM article limit 0,7`;
  db.query(sql, (err, results) => {
    if (err) return res.send({ code: 0, msg: err.message });
    if (results.length != 0) {
      results.forEach(item => {
        item.article_date = moment(item.article_date).format('YYYY-MM-DD');
      });
      // console.log(results[0].cot);
      quick = results;
    }
  });
  if (id == 0) {
    sql = `SELECT count(*) as cot FROM article`;
    db.query(sql, (err, results) => {
      if (err) return res.send({ code: 0, msg: err.message });
      if (results.length != 0) {
        // console.log(results[0].cot);
        count += results[0].cot;
      }
    });
    sql = `SELECT article.*,article_classification.ac_name FROM article,article_classification where article.ac_id = article_classification.ac_id order by article_id desc limit ${pagea},${pageb}`;
    db.query(sql, (err, results) => {
      if (err) return res.send({ code: 0, msg: err.message });
      if (results.length != 0) {
        results.forEach(item => {
          item.article_date = moment(item.article_date).format('YYYY-MM-DD');
        });
        news = results;
      }
    });
  }
  if (id == 1) {
    sql = `SELECT count(*) as cot FROM article where ac_id=1`;
    db.query(sql, (err, results) => {
      if (err) return res.send({ code: 0, msg: err.message });
      if (results.length != 0) {
        // console.log(results[0].cot);
        count += results[0].cot;
      }
    });
    sql = `SELECT article.*,article_classification.ac_name FROM article,article_classification where article.ac_id = article_classification.ac_id and article.ac_id = 1 order by article_id desc limit ${pagea},${pageb}`;
    db.query(sql, (err, results) => {
      if (err) return res.send({ code: 0, msg: err.message });
      if (results.length != 0) {
        results.forEach(item => {
          item.article_date = moment(item.article_date).format('YYYY-MM-DD');
        });
        news = results;
      }
    });
  }
  if (id == 2) {
    sql = `SELECT count(*) as cot FROM article where ac_id=2`;
    db.query(sql, (err, results) => {
      if (err) return res.send({ code: 0, msg: err.message });
      if (results.length != 0) {
        // console.log(results[0].cot);
        count += results[0].cot;
      }
    });
    sql = `SELECT article.*,article_classification.ac_name FROM article,article_classification where article.ac_id = article_classification.ac_id and article.ac_id = 2 order by article_id desc limit ${pagea},${pageb}`;
    // console.log(sql);

    db.query(sql, (err, results) => {
      if (err) return res.send({ code: 0, msg: err.message });
      if (results.length != 0) {
        // console.log(results);
        results.forEach(item => {
          item.article_date = moment(item.article_date).format('YYYY-MM-DD');
        });
        data = results;
      }
    });
  }
  setTimeout(() => {
    // console.log(quick);
    if (count == 0) {
      return res.send({ code: 0, msg: '查询到0条记录' });
    }
    // console.log(data);
    // console.log(news);
    res.render('index/article_list', { data, news, count, page, id, quick });
  }, 500);
});

// 文章的单个页面  详情页面  /news
router.get('/article', (req, res) => {
  // console.log(111);
  // console.log(req.query);
  // console.log(111);
  var { id } = req.query;
  var article;
  var upTo;
  var downTo;
  var flash;
  // 上一页
  var uptoSql = `SELECT article_id,article_name FROM article WHERE is_delete=0 AND article_id<${id} ORDER BY article_id DESC LIMIT 1`;
  db.query(uptoSql, (err, results) => {
    if (err) {
      return res.send({ code: 0, msg: err.message });
    }
    if (results.length != 0) {
      // 查到了数据
      upTo = results[0];
      // console.log(downTo);
    } else {
      // 没查到数据  说明没有数据了  可能是最后一篇；
      // return res.send({ code: 0, msg: '查询失败' });
      upTo = { article_id: id, article_name: ' 没有了' };
    }
  });
  // 下一页
  var downToSql = `SELECT article_id,article_name FROM article WHERE is_delete=0 AND article_id>${id}  LIMIT 1`;
  db.query(downToSql, (err, results) => {
    if (err) {
      return res.send({ code: 0, msg: err.message });
    }
    if (results.length != 0) {
      // 查到了数据
      downTo = results[0];
      // console.log(downTo);
    } else {
      downTo = { article_id: id, article_name: ' 没有了' };
    }
  });
  // 获取详情数据
  var sql = `SELECT * FROM article WHERE article_id=${id}`;
  db.query(sql, (err, results) => {
    if (err) {
      return res.send({ code: 0, msg: err.message });
    }
    if (results.length != 0) {
      article = results[0];
      var date = article.article_date.toLocaleDateString().split('/').join('-');
      var time = article.article_date.toLocaleTimeString().split(':').slice(1).join(':');
      article.article_date = date + ' ' + time;
      // 浏览量的修改
      const visitSQL = `UPDATE article SET article_visit=${
        article.article_visit - 0 + 1
      } WHERE article_id=${id}`;
      db.query(visitSQL, (err, results) => {
        if (err) {
          return res.send({ code: 0, msg: err.message });
        }
        if (results.affectedRows == 1) {
          article.article_visit = article.article_visit - 0 + 1;
          // console.log(article);
        } else {
          return res.send({ code: 0, msg: '查询失败' });
        }
      });
    } else {
      return res.send({ code: 0, msg: '查询失败' });
    }
  });
  // 获取快讯侧边数据
  var flashSql = `SELECT article_id,article_name,article_date FROM article WHERE is_delete=0 AND article_id!=${id} LIMIT 5`;
  db.query(flashSql, (err, results) => {
    if (err) {
      return res.send({ code: 0, msg: err.message });
    }
    if (results.length != 0) {
      results.forEach(item => {
        item.article_date = item.article_date.toLocaleDateString().split('/').join('-');
      });
      flash = results;
      // console.log(flash);
    } else {
      return res.send({ code: 0, msg: '查询失败' });
    }
  });
  setTimeout(() => {
    res.render('index/article', { article, upTo, downTo, flash });
  }, 500);
});

// 今日新闻
router.get('/news', (req, res) => {
  res.render('index/news');
});

// 合作案例
router.get('/cooperation', (req, res) => {
  res.render('index/cooperation_case');
});
// 核心团队
router.get('/team', (req, res) => {
  res.render('index/team');
});
// 关于我们  /about
router.get('/about', (req, res) => {
  res.render('index/about');
});

module.exports = router;
